﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Class works with database
/// </summary>
public static class DB
{
    public static string cnString="";
    private static SqlConnection cn;
    public static bool connect() {
        bool success = true;
        try
        {
            cn = new SqlConnection(cnString);
            cn.Open();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            success = false;
        }
        return success;
    }
    public static object getValue(string queryString)
    {
        object result = null; 
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            result = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            cn.Close();
        }
        return result;
    }
    public static object getValue(string queryString, object[,] parameter)
    {
        object result = null;
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            if (parameter != null)
                for (int i = 0; i < parameter.GetLength(0); i++)
                    cmd.Parameters.Add(new SqlParameter(parameter[i, 0].ToString(), parameter[i, 1]));
            result = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            cn.Close();
        }
        return result;
    }
    public static DataTable getData(string queryString)
    {
        DataTable dt = new DataTable();
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            dt.Load(dr);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            cn.Close();
        }
        return dt;
    }
    public static DataTable getData(string queryString, object[,] parameter)
    {
        DataTable dt = new DataTable();
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            if (parameter != null)
                for (int i = 0; i < parameter.GetLength(0); i++)
                    cmd.Parameters.Add(new SqlParameter(parameter[i, 0].ToString(), parameter[i, 1]));
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            dt.Load(dr);
         }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            cn.Close();
        }
        return dt;
    }
    public static bool exec(string queryString)
    {
        bool success = true;
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            success = false;
        }
        finally
        {
            cn.Close();
        }
        return success;
    }

    public static bool exec(string queryString, object[,] parameter)
    {
        bool success = true;
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            cmd.CommandType = CommandType.Text;
            if (parameter != null)
                for (int i = 0; i < parameter.GetLength(0); i++)
                    cmd.Parameters.Add(new SqlParameter(parameter[i, 0].ToString(), parameter[i, 1]));
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            success = false;
        }
        finally
        {
            cn.Close();
        }
        return success;
    }
    public static bool exec(string queryString, string commandText)
    {
        bool success = true;
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            switch (commandText)
            {
                case "Text":
                    cmd.CommandType = CommandType.Text;
                    break;
                case "StoredProcedure":
                    cmd.CommandType = CommandType.StoredProcedure;
                    break;
                case "TableDirect":
                    cmd.CommandType = CommandType.TableDirect;
                    break;
            }
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            success = false;
        }
        finally
        {
            cn.Close();
        }
        return success;
    }

    public static bool exec(string queryString, object[,] parameter, string commandText)
    {
        bool success = true;
        try
        {
            connect();
            SqlCommand cmd = new SqlCommand(queryString, cn);
            switch (commandText) 
            { 
                case "Text":
                    cmd.CommandType = CommandType.Text;
                    break;
                case "StoredProcedure":
                    cmd.CommandType = CommandType.StoredProcedure;
                    break;
                case "TableDirect":
                    cmd.CommandType = CommandType.TableDirect;
                    break;
            }
            if (parameter != null)
                for (int i = 0; i < parameter.GetLength(0); i++)
                    cmd.Parameters.Add(new SqlParameter(parameter[i, 0].ToString(), parameter[i, 1]));
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            success = false;
        }
        finally
        {
            cn.Close();
        }
        return success;
    }
    private static void resizeParam(ref object[,] parameter, int newRow, int newColumn)
    { 
        object [,] newArray = new object[newRow, newColumn];
        Array.Copy(parameter, newArray, parameter.Length);
        parameter = newArray;
    }
    public static void addParam(ref object[,] parameter, object paramName, object paramValue)
    {
        int curRow = parameter.GetLength(0);
        resizeParam(ref parameter, curRow + 1, 2);
        parameter[curRow, 0] = paramName;
        parameter[curRow, 1] = paramValue;
    }
}
